SELECT * from sqlinstance;
/*
SELECT * from sqlinstance;
INSERT INTO sqlinstance(name,dbstring,dbtype)VALUES('数据测试','Server=192.168.100.80;Port=5432;User Id=gpadmin;Password=pga*%2134Kmjg531#db;Database=kede_fv;CommandTimeout=0',0);
INSERT INTO sqlinstance(name,dbstring,dbtype)VALUES('Tidb','data source=192.168.100.153,1433;initial catalog=TiDB;persist security info=True;user id=wangjunyang;password=kdcfldsgfabci_.SWER233;MultipleActiveResultSets=True;',1);
SELECT * from mailcontacts;
insert into  mailcontacts(name,email)VALUES('王军洋','wangjunyang@kede.com'),('王军洋钉钉','merlinking@dingtalk.com'),('王军洋-ali','merlinking@aliyun.com'),('王军洋-outlook','merlinking@outlook.com');

*/



SELECT * from mailsender
-- INSERT INTO mailsender(server,authentication,"user",name,password,port)VALUES('smtp.kede.com',true,'wangjunyang@kede.com','王军洋','wpj19900210',25);




-- delete from emailtemplate
  SELECT * from emailtemplate
/*
INSERT INTO emailtemplate(name,mailSenderId,sendname,recipients,cc,bcc,subject,body,dataattachmenttype,attachmentsignature,multiplesheet,sheetnames,execinstance,sqlparams,sqlscript,isenable)
VALUES
(
'财务数据',1,'可得大数据组',array['merlinking@aliyun.com'],array['merlinking@outlook.com'],array['merlinking@dingtalk.com'],'2019年报表数据-销售','尊敬的用户你好，这是你需要的数据  数据表1 #财务数据#   数据表2 #明细数据#',0,'t','t',array['财务数据','明细数据'],1,array['p_startTime','p_endTime'],E'DECLARE @startTime AS DATE=\'p_startTime\',@endTime AS DATE =\'p_endTime\';SELECT SalePlatformID,SalePlatformName,PlatType,ChannelId,Createdate FROM dbo.ori_SalePlatform WHERE Createdate>@startTime AND Createdate<=@endTime;SELECT PersonalName 员工,IsNoBrand 定牌,Total 商品数,Discontinued 下线数,SubattributeNumber 子商品数,Sumstock 库存数,SumSalesAmount 销售额,SumCostAmount 销售成本,StockAmount 库存金额,StockAmountRate 周转率 FROM [dbo].[fv_Personal_Sale_Report]
WHERE TagDate >@startTime AND TagDate<=@endTime;','t'
);

-- 需要重写 SQL
INSERT INTO emailtemplate(name,mailSenderId,sendname,recipients,cc,bcc,subject,body,dataattachmenttype,attachmentsignature,multiplesheet,sheetnames,execinstance,sqlparams,sqlscript,isenable)
VALUES
(
'财务数据',1,'可得大数据组',array['merlinking@aliyun.com'],array['merlinking@outlook.com'],array['merlinking@dingtalk.com'],'2019年报表数据-销售','尊敬的用户你好，这是你需要的数据  数据表1 #财务数据#   数据表2 #明细数据#',0,'t','t',array['财务数据','明细数据'],2,array['p_startTime','p_endTime'],E'DECLARE @startTime AS DATE=\'p_startTime\',@endTime AS DATE =\'p_endTime\';SELECT SalePlatformID,SalePlatformName,PlatType,ChannelId,Createdate FROM dbo.ori_SalePlatform WHERE Createdate>@startTime AND Createdate<=@endTime;SELECT PersonalName 员工,IsNoBrand 定牌,Total 商品数,Discontinued 下线数,SubattributeNumber 子商品数,Sumstock 库存数,SumSalesAmount 销售额,SumCostAmount 销售成本,StockAmount 库存金额,StockAmountRate 周转率 FROM [dbo].[fv_Personal_Sale_Report]
WHERE TagDate >@startTime AND TagDate<=@endTime;','t'
);
*/
  SELECT * from mailcontacts
  SELECT * from emailtask;
  SELECT * from sqlinstance;
	-- 3
	  SELECT * from mailsender
	SELECT * from emailtask
	insert into emailtask(name,emailtemplateid,cronstr,centent)VALUES('邮件测试任务',3,'*/3 * * * *','临时测试 每3分钟执行一次');
	
	SELECT * FROM emailtemplate  order by id;
	
	select * from emailtask etk join emailtemplate ett on etk.emailtemplateid=ett.id
	
		insert into emailtask(taskid,centent,status)VALUES(@taskid,@centent,@status);
	
	select * from 	mailsender
	select * from 	emailtask_logs

  SELECT * from emailtask;
-- update emailtask set emailtemplateid=1;
0: 嵌到html  1:附加到xlsx文件
update emailtemplate set execinstance=2,dataattachmenttype=1 -- ,sqlscript=E'DECLARE @startTime AS DATE=\'p_startTime\',@endTime AS DATE =\'p_endTime\';SELECT top 10 SalePlatformID,SalePlatformName,PlatType,ChannelId,Createdate FROM dbo.ori_SalePlatform WHERE Createdate>@startTime AND Createdate<=@endTime;SELECT top 19 PersonalName 员工,IsNoBrand 定牌,Total 商品数,Discontinued 下线数,SubattributeNumber 子商品数,Sumstock 库存数,SumSalesAmount 销售额,SumCostAmount 销售成本,StockAmount 库存金额,StockAmountRate 周转率 FROM [dbo].[fv_Personal_Sale_Report] WHERE TagDate >@startTime AND TagDate<=@endTime;'
 
 -- ,body=E'<style>.table-c table{border-right:1px solid #000;border-bottom:1px solid #000}.table-c table td{border-left:1px solid #000;border-top:1px solid #000;padding:5px 5px 5px 5px;}.table-c table th{border-left:1px solid #000;border-top:1px solid #000;font-weight:bold;background-color:rgb(217,236,255)}</style>尊敬的用户你好，这是你需要的数据  数据表1 #财务数据#   数据表2 #明细数据#'
 -- ,subject='自动化邮件测试-2019年报表数据-销售'
 -- ,cc=array['merlinking@aliyun.com']
where id=1;


-- Unknown = 0,Postgres = 1,SqlServer =2,
update sqlinstance set dbtype=1 where id=1;
update sqlinstance set dbtype=2 where id=2;
  SELECT * from sqlinstance;




